﻿Public Class clsPersona

    Private NroDoc As Integer
    Private TipoDoc As Integer

    Private Apellido As String
    Private Nombres As String
    Private FechaNacimiento As Date
    Private EstadoCivil As String

    Private Telefono As String
    Private Celular As String

    Public Property pNroDoc() As Integer
        Get
            Return NroDoc
        End Get
        Set(ByVal value As Integer)
            NroDoc = value
        End Set
    End Property

    Public Property pTipoDoc() As Integer
        Get
            Return TipoDoc
        End Get
        Set(ByVal value As Integer)
            TipoDoc = value
        End Set
    End Property

    Public Property pApellido() As String
        Get
            Return Apellido
        End Get
        Set(ByVal value As String)
            Apellido = value
        End Set
    End Property

    Public Property pNombres() As String
        Get
            Return Nombres
        End Get
        Set(ByVal value As String)
            Nombres = value
        End Set
    End Property

    Public Property pFechaNacimiento() As Date
        Get
            Return FechaNacimiento
        End Get
        Set(ByVal value As Date)
            FechaNacimiento = value
        End Set
    End Property
    Public Property pEstadoCivil() As String
        Get
            Return EstadoCivil
        End Get
        Set(ByVal value As String)
            EstadoCivil = value
        End Set
    End Property

    Public Property pTelefono() As String
        Get
            Return Telefono
        End Get
        Set(ByVal value As String)
            Telefono = value
        End Set
    End Property

    Public Property pCelular() As String
        Get
            Return Celular
        End Get
        Set(ByVal value As String)
            Celular = value
        End Set
    End Property

    'la siguiete propiedad es para los mensajes de error
    Private MsgPersona As String
    Public Property pMsgPersona() As String
        Get
            Return MsgPersona
        End Get
        Set(ByVal value As String)
            MsgPersona = value
        End Set
    End Property

    Public Enum VerifExistType
        Dni = 0
        TipoDNI = 1
        Ambos = 2
    End Enum


    Private dbConexion As clsConexion
    Public Sub New()
        dbConexion = New clsConexion
    End Sub
    Public Enum TipoQuery
        InfoTodo = 0
        InfoIndividual = 1
        InfoDesempleados = 2
    End Enum

    Public dbPerDataRow As DataRow
    Public dbPerDataSet As DataSet

    Public Function fn_EjecutarQuery(ByVal Tipo As TipoQuery) As Boolean
        Try
            Dim EjecutarQuery As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then
                Select Case Tipo
                    Case TipoQuery.InfoIndividual
                        SqlQuery = " select * from Persona " & _
                                   " where  NroDoc = @NroDoc and TipoDoc = @TipoDoc"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@NroDoc").Value = pNroDoc
                        dbConexion.dbcommand.Parameters("@TipoDoc").Value = pTipoDoc
                        dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                        dbConexion.dbdataset = New DataSet
                        dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Persona")
                        If dbConexion.dbdataset.Tables.Count > 0 Then
                            EjecutarQuery = True
                            dbPerDataRow = dbConexion.dbdataset.Tables("Persona").Rows(0)
                            dbConexion.dbdataadapter.Dispose()
                            dbConexion.dbdataset.Dispose()
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            MsgPersona = "Error al obtener la información de la persona."
                        End If
                    Case TipoQuery.InfoTodo
                        SqlQuery = " select * from Persona"
                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                        dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                        dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Persona")
                        If dbConexion.dbdataset.Tables.Count > 0 Then
                            EjecutarQuery = True
                            dbPerDataSet = dbConexion.dbdataset.Copy()
                            dbConexion.dbdataadapter.Dispose()
                            dbConexion.dbdataset.Dispose()
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            MsgPersona = "Error al obtener la información de las personas."
                        End If

                    Case TipoQuery.InfoDesempleados
                        SqlQuery = "SELECT Nombres,Apellido,Persona.NroDoc,Persona.TipoDoc FROM Persona LEFT OUTER JOIN Empleado ON Persona.NroDoc = Empleado.NroDoc and Persona.TipoDoc = Empleado.TipoDoc WHERE Empleado.Legajo IS NULL"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                        dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                        dbConexion.dbdataset = New DataSet
                        dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Persona")
                        If dbConexion.dbdataset.Tables.Count > 0 Then
                            EjecutarQuery = True
                            dbPerDataSet = dbConexion.dbdataset.Copy()
                            dbConexion.dbdataadapter.Dispose()
                            dbConexion.dbdataset.Dispose()
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            MsgPersona = "No hay personas cargadas e inactivas."
                        End If

                End Select
            Else
                MsgPersona = "No se pudo establecer la conexión con la bse de datos."
            End If
            Return (EjecutarQuery)
        Catch ex As Exception
            MsgPersona = "Ocurrio un problema al ejecutar la consulta." & vbCrLf & ex.Message
            Return (False)
        End Try
    End Function
    Public Function fn_ValidarDatos(ByVal Operacion As String) As Boolean
        Select Case Operacion
            Case "Add", "Upd", "Del"
                If Trim(NroDoc) = 0 Then
                    MsgPersona = "Debe proporcionar el DNI de la Persona"
                    Return (False)
                End If
                If Trim(TipoDoc) = 0 Then
                    MsgPersona = "Debe proporcionar el Tipo DNI de la persona"
                    Return (False)
                End If
        End Select

        Select Case Operacion
            Case "Add", "Upd"
                If Trim(Apellido) = "" Then
                    MsgPersona = "Debe proporcionar el Apellido"
                    Return (False)
                End If

                If Trim(Nombres) = "" Then
                    MsgPersona = "Debe proporcionar el o los Nombres"
                    Return (False)
                End If

                If Trim(FechaNacimiento) = "" Then
                    MsgPersona = "Debe proporcionar la Fecha de Nacimiento"
                    Return (False)
                End If

                If Trim(EstadoCivil) = "" Then
                    MsgPersona = "Debe proporcionar el Estado Civil"
                    Return (False)
                End If

                If Trim(Telefono) = "" Then
                    If Trim(Celular) = "" Then
                        MsgPersona = "Debe proporcionar al menos un telefono para contacto"
                        Return (False)
                    End If
                End If

        End Select
        Return (True)
    End Function

    Public Function fn_AddPersona() As Boolean
        Try
            Dim EjecucionOk As Boolean = False
            Dim SqlQuery As String

            If fn_ValidarDatos("Add") Then
                If dbConexion.fn_abrir Then
                    SqlQuery = " insert into Persona(Apellido,NroDoc,Telefono,Celular,TipoDoc,FechaNacimiento,EstadoCivil,Nombres) " & _
                               " values(@Apellido,@NroDoc,@Telefono,@Celular,@TipoDoc,@FechaNacimiento,@EstadoCivil,@Nombres) "
                    dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                    With dbConexion.dbcommand
                        .CommandType = CommandType.Text
                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Apellido", Data.SqlDbType.VarChar, 20))
                        .Parameters("@Apellido").Value = pApellido()

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        .Parameters("@NroDoc").Value = pNroDoc

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Telefono", Data.SqlDbType.VarChar, 20))
                        .Parameters("@Telefono").Value = pTelefono

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Celular", Data.SqlDbType.VarChar, 20))
                        .Parameters("@Celular").Value = pCelular

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        .Parameters("@TipoDoc").Value = pTipoDoc

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@FechaNacimiento", Data.SqlDbType.DateTime))
                        .Parameters("@FechaNacimiento").Value = pFechaNacimiento

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@EstadoCivil", Data.SqlDbType.VarChar, 20))
                        .Parameters("@EstadoCivil").Value = pEstadoCivil

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Nombres", Data.SqlDbType.VarChar, 50))
                        .Parameters("@Nombres").Value = pNombres

                        If .ExecuteNonQuery() > 0 Then
                            EjecucionOk = True
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            MsgPersona = "Se obtuvo errores al agregar la Persona."
                            EjecucionOk = False
                        End If
                    End With
                Else
                    EjecucionOk = False
                End If
            Else
                EjecucionOk = False
            End If
            Return (EjecucionOk)
        Catch ex As Exception
            MsgPersona = "Error al agregar la Persona." & vbCrLf & ex.Message
            If dbConexion.dbconnection.State = ConnectionState.Open Then dbConexion.su_cerrar()
            Return (False)
        End Try
    End Function
    Public Function fn_DelPersona() As Boolean
        Try
            Dim EjecucionOk As Boolean = False
            Dim SqlQuery As String
            If fn_ValidarDatos("Del") Then
                If dbConexion.fn_abrir Then

                    SqlQuery = " delete from  Persona " & _
                               " where   NroDoc=@NroDoc and TipoDoc=@TipoDoc "
                    dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                    With dbConexion.dbcommand
                        .CommandType = CommandType.Text
                        .Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        .Parameters("@NroDoc").Value = pNroDoc

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        .Parameters("@TipoDoc").Value = pTipoDoc

                        If .ExecuteNonQuery() > 0 Then
                            EjecucionOk = True
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            MsgPersona = "Se obtuvo errores al eliminar la Persona"
                            EjecucionOk = False
                        End If
                    End With
                Else
                    EjecucionOk = False
                End If
            Else
                EjecucionOk = False
            End If
            Return (EjecucionOk)
        Catch ex As Exception
            MsgPersona = "Error al eliminar el Usuario." & vbCrLf & ex.Message
            If dbConexion.dbconnection.State = ConnectionState.Open Then dbConexion.su_cerrar()
            Return (False)
        End Try
    End Function

    Public Function fn_UpdPersona() As Boolean
        Try
            Dim EjecucionOk As Boolean = False
            Dim SqlQuery As String
            If fn_ValidarDatos("Upd") Then
                If dbConexion.fn_abrir Then
                    SqlQuery = " update  Persona " & _
                               " set apellido = @Apellido, Telefono = @Telefono,Celular = @Celular, FechaNacimiento = @FechaNacimiento,EstadoCivil = @EstadoCivil, Nombres = @Nombres" & _
                               " where   NroDoc = @NroDoc and TipoDoc = @TipoDoc "
                    dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)
                    With dbConexion.dbcommand
                        .CommandType = CommandType.Text
                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Apellido", Data.SqlDbType.VarChar, 20))
                        .Parameters("@Apellido").Value = pApellido

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        .Parameters("@NroDoc").Value = pNroDoc

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Telefono", Data.SqlDbType.VarChar, 20))
                        .Parameters("@Telefono").Value = pTelefono

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Celular", Data.SqlDbType.VarChar, 20))
                        .Parameters("@Celular").Value = pCelular

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        .Parameters("@TipoDoc").Value = pTipoDoc

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@FechaNacimiento", Data.SqlDbType.Date))
                        .Parameters("@FechaNacimiento").Value = pFechaNacimiento

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@EstadoCivil", Data.SqlDbType.VarChar, 20))
                        .Parameters("@EstadoCivil").Value = pEstadoCivil

                        .Parameters.Add(New Data.SqlClient.SqlParameter("@Nombres", Data.SqlDbType.VarChar, 50))
                        .Parameters("@Nombres").Value = pNombres

                        If .ExecuteNonQuery() > 0 Then
                            EjecucionOk = True
                            dbConexion.dbcommand.Dispose()
                            dbConexion.su_cerrar()
                        Else
                            MsgPersona = "Se obtuvo errores al modificar La Persona."
                            EjecucionOk = False
                        End If
                    End With
                Else
                    EjecucionOk = False
                End If
            Else
                EjecucionOk = False
            End If
            Return (EjecucionOk)
        Catch ex As Exception
            MsgPersona = "Error al agregar la Persona." & vbCrLf & ex.Message
            If dbConexion.dbconnection.State = ConnectionState.Open Then dbConexion.su_cerrar()
            Return (False)
        End Try
    End Function

    Public Function fn_ExistePersona(ByVal TipoVerificacion As VerifExistType) As Boolean
        Try
            Dim Existe As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then

                Select Case TipoVerificacion
                    Case VerifExistType.Ambos
                        SqlQuery = " select * from Persona " & _
                                   " where   NroDoc = @NroDoc and TipoDoc= @TipoDoc"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@NroDoc").Value = pNroDoc

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@TipoDoc").Value = pTipoDoc

                        dbConexion.dbcommand.CommandType = CommandType.Text
                        MsgPersona = "El la Persona no Existe."
                End Select

                dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                dbConexion.dbdataset = New DataSet
                dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Persona")

                If dbConexion.dbdataset.Tables.Count > 0 Then
                    If dbConexion.dbdataset.Tables("Persona").Rows.Count > 0 Then
                        Existe = True
                        MsgPersona = "La Persona Existe."
                    End If
                    dbPerDataSet = dbConexion.dbdataset.Copy()
                    dbConexion.dbdataadapter.Dispose()
                    dbConexion.dbdataset.Dispose()
                    dbConexion.dbcommand.Dispose()
                    dbConexion.su_cerrar()
                Else
                    MsgPersona = "La Persona no Existe."
                    Existe = False
                End If
            End If
            Return (Existe)
        Catch ex As Exception
            MsgPersona = "Error al buscar si existe la persona." & vbCrLf & ex.Message
            Return (False)
        End Try

    End Function

    Public Function fn_ExisteEmpleado(ByVal TipoVerificacion As VerifExistType) As Boolean
        Try
            Dim Existe As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then

                Select Case TipoVerificacion
                    Case VerifExistType.Ambos
                        SqlQuery = " select * from Empleado " & _
                                   " where   NroDoc=@NroDoc and TipoDoc=@TipoDoc"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@NroDoc").Value = pNroDoc

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@TipoDoc").Value = pTipoDoc

                        dbConexion.dbcommand.CommandType = CommandType.Text
                End Select

                dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                dbConexion.dbdataset = New DataSet
                dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Empleado")

                If dbConexion.dbdataset.Tables.Count > 0 Then
                    If dbConexion.dbdataset.Tables("Empleado").Rows.Count > 0 Then
                        Existe = True
                    End If
                    dbPerDataSet = dbConexion.dbdataset.Copy()
                    dbConexion.dbdataadapter.Dispose()
                    dbConexion.dbdataset.Dispose()
                    dbConexion.dbcommand.Dispose()
                    dbConexion.su_cerrar()
                Else
                    Existe = False
                End If
            End If
            Return (Existe)
        Catch ex As Exception
            MsgPersona = "Error al buscar si existe el empleado." & vbCrLf & ex.Message
            Return (False)
        End Try

    End Function



    Public Function fn_ExisteDireccion(ByVal TipoVerificacion As VerifExistType) As Boolean
        Try
            Dim Existe As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then

                Select Case TipoVerificacion
                    Case VerifExistType.Ambos
                        SqlQuery = " select * from Direccion " & _
                                   " where   NroDoc=@NroDoc and TipoDoc=@TipoDoc"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@NroDoc").Value = pNroDoc

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@TipoDoc").Value = pTipoDoc

                        dbConexion.dbcommand.CommandType = CommandType.Text
                End Select

                dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                dbConexion.dbdataset = New DataSet
                dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Direccion")

                If dbConexion.dbdataset.Tables.Count > 0 Then
                    If dbConexion.dbdataset.Tables("Direccion").Rows.Count > 0 Then
                        Existe = True
                    End If
                    dbPerDataSet = dbConexion.dbdataset.Copy()
                    dbConexion.dbdataadapter.Dispose()
                    dbConexion.dbdataset.Dispose()
                    dbConexion.dbcommand.Dispose()
                    dbConexion.su_cerrar()
                Else
                    Existe = False
                End If
            End If
            Return (Existe)
        Catch ex As Exception
            MsgPersona = "Error al buscar si existe una Direccion." & vbCrLf & ex.Message
            Return (False)
        End Try

    End Function


    Public Function fn_ExisteCurriculum(ByVal TipoVerificacion As VerifExistType) As Boolean
        Try
            Dim Existe As Boolean = False
            Dim SqlQuery As String
            If dbConexion.fn_abrir Then

                Select Case TipoVerificacion
                    Case VerifExistType.Ambos
                        SqlQuery = " select * from Curriculum " & _
                                   " where   NroDoc=@NroDoc and TipoDoc=@TipoDoc"

                        dbConexion.dbcommand = New Data.SqlClient.SqlCommand(SqlQuery, dbConexion.dbconnection)

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@NroDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@NroDoc").Value = pNroDoc

                        dbConexion.dbcommand.Parameters.Add(New Data.SqlClient.SqlParameter("@TipoDoc", Data.SqlDbType.Int))
                        dbConexion.dbcommand.Parameters("@TipoDoc").Value = pTipoDoc

                        dbConexion.dbcommand.CommandType = CommandType.Text
                End Select

                dbConexion.dbdataadapter = New SqlClient.SqlDataAdapter(dbConexion.dbcommand)
                dbConexion.dbdataset = New DataSet
                dbConexion.dbdataadapter.Fill(dbConexion.dbdataset, "Curriculum")

                If dbConexion.dbdataset.Tables.Count > 0 Then
                    If dbConexion.dbdataset.Tables("Curriculum").Rows.Count > 0 Then
                        Existe = True
                    End If
                    dbPerDataSet = dbConexion.dbdataset.Copy()
                    dbConexion.dbdataadapter.Dispose()
                    dbConexion.dbdataset.Dispose()
                    dbConexion.dbcommand.Dispose()
                    dbConexion.su_cerrar()
                Else
                    Existe = False
                End If
            End If
            Return (Existe)
        Catch ex As Exception
            MsgPersona = "Error al buscar si existe el Curriculum." & vbCrLf & ex.Message
            Return (False)
        End Try

    End Function


End Class
